/*
 * BulkMessageDaoImpl.java
 *
 * Created on February 5, 2005, 1:17 AM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */
package org.naga.bulksender.dal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.naga.bulksender.data.BulkMessage;
import org.naga.bulksender.data.SMSBulkMessage;
import org.smslib.InboundMessage;
import org.smslib.*;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.transaction.annotation.Transactional;

/**
 * Data Layer Implementation
 * @author Naga Services
 */
public class BulkMessageDaoImpl extends SimpleJdbcDaoSupport implements BulkMessageDaoIfc {

    private Log log = LogFactory.getLog(BulkMessageDaoImpl.class);

    /**
     * jj
     */
    @Transactional(readOnly = true)
    public List<BulkMessage> checkNewMessages() {
        log.debug("Checking For New Messages");
        return null;
    }

    
    /**
     * Get the new messages in the Database. For each message 
     * create a new Adapter.  The message status should be New.
     *
     * @throws org.springframework.dao.DataAccessException 
     * @return The list of new Messages identified
     */
    @Transactional(readOnly = true)
    public List<BulkMessage> getNewMessages() throws DataAccessException {
        log.debug("Search for New Messages");
        return getSimpleJdbcTemplate().query(
                "SELECT MSG.ID, MSG.TEXTO, MSG.NUMEROS FROM MESSAGE MSG where MSG.MSGSTATUS='New'",
                new ParameterizedRowMapper<BulkMessage>() {

                    public BulkMessage mapRow(ResultSet rs, int rowNum) throws SQLException {
                        int smsId = rs.getInt(1);
                        String text = rs.getString(2);
                        String numbers = rs.getString(3);
                        List<String> lstManully = null;
                        if (numbers != null && !"".equals(numbers)) {
                            //Split the string added in the phone manually 
                            lstManully = Arrays.asList(StringUtils.split(numbers, ";"));
                        }
                        return new SMSBulkMessage(text, smsId, lstManully);
                    }
                });
    }

    /**
     * Based in MsgId idenitfy all contacts that user selected to send this email
     * once idenitfied the contact, we get the list of phone number from the contact
     * in active status
     * @param msg 
     * @throws org.springframework.dao.DataAccessException 
     */
    @Transactional(readOnly = true)
    public void getNumbersFromContacts(BulkMessage msg) throws DataAccessException {
        List<String> lstNumbers = getSimpleJdbcTemplate().query(
                " SELECT PHO.NUMBER  FROM MESSAGE MSG , MESSAGE_CONTACT MSG_CONT, CONTACT CONT, PHONE PHO " +
                " WHERE    MSG.ID = ?  AND     MSG.ID = MSG_CONT.MESSAGE_ID " +
                " AND     MSG_CONT.CONTACTOS_ID = CONT.ID " +
                " AND     CONT.ID = PHO.CONTACT_ID " +
                " AND     PHO.ACTIVE = true",
                new ParameterizedRowMapper<String>() {

                    public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                        String number = rs.getString(1);
                        return number;
                    }
                }, new Object[]{msg.getBulkSmsId()});
        msg.setLstNumberFromContacts(lstNumbers);
    }

    /**
     * Based in the Msg get the list of groups sellected to send this msg
     * once get th groups, indeitfy the contact and finally the 
     * phone number in active status  of the contacts contained in these groups.
     * @param msg 
     * @throws org.springframework.dao.DataAccessException 
     */
    @Transactional(readOnly = true)
    public void getNumbersFromGroup(BulkMessage msg) throws DataAccessException {
        List<String> lstNumbers = getSimpleJdbcTemplate().query(
                " SELECT  PHO.NUMBER " +
                " FROM MESSAGE MSG , MESSAGE_MYGROUP MSG_GRP, CONTACT CONT  , PHONE PHO " +
                " WHERE  MSG.ID = ? " +
                " AND  MSG.ID = MSG_GRP.MESSAGE_ID " +
                " AND  MSG_GRP.GRUPOS_ID = CONT.MYGROUP_ID " +
                " AND  CONT.ID = PHO.CONTACT_ID " +
                " AND  PHO.ACTIVE = true",
                new ParameterizedRowMapper<String>() {

                    public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                        String number = rs.getString(1);
                        return number;
                    }
                }, new Object[]{msg.getBulkSmsId()});

        msg.setLstNumberFromGroups(lstNumbers);
    }

    
    /**
     * Change the status of the Msg based in the MsgId
     * New, Scheduled, Sending, Sent
     * @param msgId 
     * @param newStatus 
     * @throws org.springframework.dao.DataAccessException 
     */
    @Transactional
    public void changeMessageStatus(Integer msgId, String newStatus) throws DataAccessException {
        log.debug("Change Msg Status " + msgId + " to " + newStatus);
        getSimpleJdbcTemplate().update("UPDATE Message SET MSGSTATUS = ?  where ID = ?",
                new Object[]{newStatus, msgId});
    }

    /**
     * Save the all sms sent in a Log Table
     * Finally we don't if the msg is sent to groups or contact
     * we need to log each sms sent
     * @param msgId 
     * @param phoneNumber 
     * @throws org.springframework.dao.DataAccessException 
     */
    @Transactional
    public void logMsgSent(Integer msgId, String phoneNumber) throws DataAccessException {
        log.debug("Log Msg " + msgId + " sent to " + phoneNumber);
        getSimpleJdbcTemplate().update("insert into msg_sent_logs values (NULL,?, ?,now())",
                new Object[]{msgId, phoneNumber});
    }

    /**
     * Save a Msg, getting the MSG ans assign to Adapter
     * @param msg 
     * @throws org.springframework.dao.DataAccessException 
     */
    @Transactional
    public void store(Message msg) throws DataAccessException {
        String phoneNumber = null;
        if (msg instanceof InboundMessage) {
            InboundMessage iMsg = (InboundMessage) msg;
            phoneNumber = iMsg.getOriginator();

        } else {
            OutboundMessage oMsg = (OutboundMessage) msg;
            phoneNumber = oMsg.getRecipient();
            
        }
        log.debug("Saving msg Received :>" + msg.getId());
        getSimpleJdbcTemplate().update(
                "INSERT INTO MESSAGE(ID,VERSION,NUMEROS,TEXTO,MSGTYPE,CREATEDON,MSGCREATEDDATE,MSGPRIORITY) " +
                "VALUES(NULL,0,?,?,?,NOW(),NOW(),'Low')",
                new Object[]{phoneNumber, msg.getText(), msg.getType().toString()});

        // long idMsgSaved = getSimpleJdbcTemplate().queryForLong("call identity()");
        long idMsgSaved = getSimpleJdbcTemplate().queryForLong("SELECT LAST_INSERT_ID()");
        msg.setId(idMsgSaved + "");

    }
}
